﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using CMP.ERP.Model;
using System.Data;
using CMP.ERP.Helper;
using System.Configuration;
using DotNet.Utilities;

namespace CMP.ERP.DAL
{
    /// <summary>
    /// 订单信息
    /// </summary>
    public class ShipmentsDAL
    {
        /// <summary>
        /// 实例化SQLHelper对象
        /// </summary>
        SQLHelper p = new SQLHelper();
        /// <summary>
        /// 连接数据库字符串
        /// </summary>
        string connSql = "Data Source=169.254.4.161;Initial Catalog=FourGroupERP;User ID=sa;Pwd=111111";

        /// <summary>
        /// 显示订单信息
        /// </summary>
        /// <returns></returns> 
        public List<Shipments> ShowShipments()
        {
            SqlConnection con = new SqlConnection(connSql);
            con.Open();
            string sql = "select Shipments_Address,purchaser,Shipments_Id,Shipments_data,Shipments_Per,Shipments_Phone,Shipments_State,Express_Company_Name,WareHose_Name,Shipments_SumMoney from Shipments join WareHose on Shipments.WareHose_Id=WareHose.WareHose_Id  join ExpressCompany on Shipments.Express_Company_Id=ExpressCompany.Express_Company_Id order by Shipments_data DESC";
            SqlCommand com = new SqlCommand(sql, con);
            SqlDataAdapter adapter = new SqlDataAdapter(com);
            DataTable table = new DataTable();
            adapter.Fill(table);
            List<Shipments> list = new List<Shipments>();
            //导出的地址路径
            //ExcelHelper.OutputToExcel(table, "D:\\111.xls");
            
            foreach (DataRow item in table.Rows)
            {
                Shipments s = new Shipments();
                s.Shipments_Id = Convert.ToInt32(item["Shipments_Id"]);
                s.Shipments_data =item["Shipments_data"].ToString();
                s.Shipments_Address = item["Shipments_Address"].ToString();
                s.Shipments_Per = item["Shipments_Per"].ToString();
                s.Shipments_Phone = item["Shipments_Phone"].ToString();
                s.Shipments_State = item["Shipments_State"].ToString();
                s.Express_Company_Name = item["Express_Company_Name"].ToString();
                s.WareHose_Name = item["WareHose_Name"].ToString();
                s.Shipments_SumMoney = Convert.ToInt32(item["Shipments_SumMoney"]);
                s.purchaser = item["purchaser"].ToString();
                list.Add(s);
            }
            return list;
        }
        /// <summary>
        /// 根据年月日
        /// </summary>
        /// <param name="Year"></param>
        /// <param name="Month"></param>
        /// <param name="Day"></param>
        /// <returns></returns>
        public DataTable SelShipments(string  StartTime, string End_Time, string Shipments_Per, string Shipments_Phone)
        {
            string str = string.Format("select Shipments_Address,purchaser,Shipments_Id,Shipments_data,Shipments_Per,Shipments_Phone,Shipments_State,Express_Company_Name,WareHose_Name,Shipments_SumMoney from Shipments join WareHose on Shipments.WareHose_Id=WareHose.WareHose_Id  join ExpressCompany on Shipments.Express_Company_Id=ExpressCompany.Express_Company_Id where 1=1");
            if (StartTime != "" && End_Time != "")
            {
                if (Convert.ToDateTime(StartTime).ToString("YYYY-MM-DD") != "" && Convert.ToDateTime(End_Time).ToString("YYYY-MM-DD") != "")
                {
                    str += "and Shipments.Shipments_data between '" + StartTime + "' and '" + End_Time + "' and Shipments.purchaser like '%" + Shipments_Per + "%' and Shipments.Shipments_Phone like '%" + Shipments_Phone + "%'";
                }
                
            }
            else
            {
                str += "and Shipments.purchaser like '%" + Shipments_Per + "%' and Shipments.Shipments_Phone like '%" + Shipments_Phone + "%' ";
            }
            str += "ORDER BY Shipments_data DESC";
          

            return p.ExecDataTable(str);
        }
        public DataTable SelYear(string StartTime, string End_Time)
        {
            StringBuilder buil = new StringBuilder();
            buil.Append("Proc_Year");
            SqlParameter[] par = new SqlParameter[]{
                new SqlParameter("@Shipments_data1",StartTime),
                new SqlParameter("@Shipments_data2",End_Time)
            };
            return p.ExecDataTable_Proc(buil.ToString(), par);
        }
        public DataTable SelMonth(string StartTime, string End_Time)
        {
            StringBuilder buil = new StringBuilder();
            buil.Append("Proc_Month");
            SqlParameter[] par = new SqlParameter[]{
                new SqlParameter("@Shipments_data1",StartTime),
                new SqlParameter("@Shipments_data2",End_Time)
            };
            return p.ExecDataTable_Proc(buil.ToString(), par);
        }
        public DataTable SelDay(string StartTime, string End_Time)
        {
            StringBuilder buil = new StringBuilder();
            buil.Append("Proc_Day");
            SqlParameter[] par = new SqlParameter[]{
                new SqlParameter("@Shipments_data1",StartTime),
                new SqlParameter("@Shipments_data2",End_Time)
            };
            return p.ExecDataTable_Proc(buil.ToString(), par);
        }
        /// <summary>
        /// 购买订单信息
        /// </summary>
        /// <param name="model"></param>
        /// <param name="list"></param>
        public List<int> AddShipments(Shipments model, List<ShipmentMate> list)
        {
            List<string> cmdTextList = new List<string>();
            List<List<SqlParameter>> cmdParametersList = new List<List<SqlParameter>>();
            foreach (var item in list)
            {
                model.Shipments_SumMoney += item.Commodity_Price * item.ShipmentMate_Num;
               
                    model.Shipments_State = "未发货";
            }
             StringBuilder s = new StringBuilder();
             s.Append("insert into Shipments(Shipments_data,Shipments_Per,Shipments_Phone,Shipments_State,Express_Company_Id,WareHose_Id,Shipments_SumMoney,Shipments_Address,purchaser)");
             s.Append("values(@Shipments_data,@Shipments_Per,@Shipments_Phone,@Shipments_State,@Express_Company_Id,@WareHose_Id,@Shipments_SumMoney,@Shipments_Address,@purchaser);select @@identity");
            SqlParameter[] parameters = {
                                  new SqlParameter("@Shipments_data",SqlDbType.DateTime),
                                  new SqlParameter("@Shipments_Per",SqlDbType.VarChar,70),
                                  new SqlParameter("@Shipments_Phone",SqlDbType.VarChar,50),
                                  new SqlParameter("@Shipments_State",SqlDbType.VarChar,50),
                                  new SqlParameter("@Express_Company_Id",SqlDbType.Int),
                                  new SqlParameter("@WareHose_Id",SqlDbType.Int),
                                  new SqlParameter("@Shipments_SumMoney",SqlDbType.Int),           
                                  new SqlParameter("@Shipments_Address",SqlDbType.VarChar,70),
                                  new SqlParameter("@purchaser",SqlDbType.VarChar,70),   
                                        };
            parameters[0].Value = model.Shipments_data;
            parameters[1].Value = model.Shipments_Per;
            parameters[2].Value = model.Shipments_Phone;
            parameters[3].Value = model.Shipments_State;
            parameters[4].Value = model.Express_Company_Id;
            parameters[5].Value = model.WareHose_Id;
            parameters[6].Value = model.Shipments_SumMoney;
            parameters[7].Value = model.Shipments_Address;
            parameters[8].Value = model.purchaser;

            int Shipments_Id = Convert.ToInt32(p.ExecScalar(s.ToString(),parameters));
            //Modify End

            //子表添加数据
            //Author: JiXiang 20120419
            //Modify Begin
            StringBuilder strSqlSub = new StringBuilder();
            strSqlSub.Append("insert into ShipmentMate(");
            strSqlSub.Append(" Shipments_Id,Commodity_Id,ShipmentMate_Num) ");
            strSqlSub.Append(" values(@Shipments_Id,@Commodity_Id,@ShipmentMate_Num)");
            if (list != null)
            {
                foreach (ShipmentMate m in list)
                {
                    m.Shipments_Id = Shipments_Id;
                    SqlParameter[] parametersSub = {
                   
                     new SqlParameter("@Shipments_Id", SqlDbType.Int),
                     new SqlParameter("@Commodity_Id", SqlDbType.Int),
                     new SqlParameter("@ShipmentMate_Num",SqlDbType.Int),
                                                    };
                    parametersSub[0].Value = m.Shipments_Id;
                    parametersSub[1].Value = m.Commodity_Id;
                    parametersSub[2].Value = m.ShipmentMate_Num;
                    cmdTextList.Add(strSqlSub.ToString());
                    cmdParametersList.Add(parametersSub.ToList<SqlParameter>());
                }
            }
            return  p.ExecTransaction(cmdTextList, cmdParametersList);
        }     
        /// <summary>
        /// 查看购买订单信息  
        /// </summary>
        /// <param name="Shipments_Id"></param>
        /// <returns></returns>                                                                                                       
        public DataTable DetailsShow(int id)
        {
            string sql = "DetailsShipments";
            SqlParameter[] commandParameters = new SqlParameter[]
            {
                 new SqlParameter("@Shipments_Id",id),      
            };
            return p.ExecDataTable_Proc(sql, commandParameters);        
        }
        /// <summary>
        /// 根据编号获取
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable ShowXiangqing1(int id)
        {
            string ProcName = "DetailsShipments1";
            SqlParameter[] par = new SqlParameter[1]
           {
               new SqlParameter("@Shipments_Id",id)
           };
            return p.ExecDataTable_Proc(ProcName, par);
        }    
        
        /// <summary>
       /// 审核出库
       /// </summary>
       /// <param name="StockOrderID"></param>
       /// <param name="listPurchaseOrderSheetInfo"></param>
       /// <returns></returns>
        public List<int> Update(int Shipments_Id, List<ShipmentMate> listpor)
       {
           List<string> cmdTextList = new List<string>();
           List<List<SqlParameter>> cmdParametersList = new List<List<SqlParameter>>();
           StringBuilder updInvet = new StringBuilder();
           updInvet.Append("update Stock set Stock_Num=");
           updInvet.Append("(select Stock_Num from Stock where Commodity_Id=@Commodity_Id and WareHose_Id=@WareHose_Id)-(select ShipmentMate_Num from ShipmentMate where Commodity_Id=@Commodity_Id and Shipments_Id=@Shipments_Id) ");
           updInvet.Append("  where Commodity_Id=@Commodity_Id and WareHose_Id=@WareHose_Id");
           if (listpor != null)
           {
               foreach (ShipmentMate item in listpor)
               {
                   SqlParameter[] updsqlparameter ={
                                                       new SqlParameter("@Commodity_Id",SqlDbType.Int),
                                                       new SqlParameter("@WareHose_Id",SqlDbType.Int),
                                                       new SqlParameter("@Shipments_Id",SqlDbType.Int)
                                                   };
                   updsqlparameter[0].Value = item.Commodity_Id;
                   updsqlparameter[1].Value = erpop(Shipments_Id).WareHose_Id;
                   updsqlparameter[2].Value = Shipments_Id;
                   cmdTextList.Add(updInvet.ToString());
                   cmdParametersList.Add(updsqlparameter.ToList<SqlParameter>());
               }
           }
           StringBuilder updState = new StringBuilder();
           updState.Append("update Shipments set Shipments_State='已发货'");
           updState.Append("  where Shipments_Id=@Shipments_Id");
           
                   SqlParameter[] updsqlparameters ={
                                                       new SqlParameter("@Shipments_Id",SqlDbType.Int)
                                                   };
                   updsqlparameters[0].Value = Shipments_Id;
                   cmdTextList.Add(updState.ToString());
                   cmdParametersList.Add(updsqlparameters.ToList<SqlParameter>());

           return p.ExecTransaction(cmdTextList, cmdParametersList);
       }
        /// <summary>
        /// 根据订单编号获取仓库编号
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Shipments erpop(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select WareHose_Id from Shipments where Shipments_Id=@Shipments_Id");

            SqlParameter[] parametersSub = { 
                                         new SqlParameter("@Shipments_Id",SqlDbType.Int)  
                                           };
            parametersSub[0].Value = id;

            SqlDataReader sdr = p.ExecReader(strSql.ToString(), parametersSub);
            Shipments ert = new Shipments();
            while (sdr.Read())
            {
                ert.WareHose_Id = Convert.ToInt32(sdr["WareHose_Id"]);
            }
            return ert;
        }
        /// <summary>
        /// 详细信息
        /// </summary>
        /// <param name="PorMaid"></param>
        /// <returns></returns>
        public List<ShipmentMate> huoquiry(int ShipmentMate_Id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select Shipments_Id,Commodity_Id,ShipmentMate_Num from ShipmentMate where ShipmentMate_Id=@ShipmentMate_Id");

            SqlParameter[] parametersSub = { 
                                         new SqlParameter("@ShipmentMate_Id",SqlDbType.Int)  
                                           };
            parametersSub[0].Value = ShipmentMate_Id;

            DataTable dt = p.ExecDataTable(strSql.ToString(), parametersSub);
            List<ShipmentMate> list = new List<ShipmentMate>();
            foreach (DataRow item in dt.Rows)
            {
                ShipmentMate ps = new ShipmentMate();
                ps.Commodity_Id = Convert.ToInt32(item["Commodity_Id"]);
                ps.Shipments_Id = Convert.ToInt32(item["Shipments_Id"]);
                ps.ShipmentMate_Num = Convert.ToInt32(item["ShipmentMate_Num"]);

                list.Add(ps);
            }
            return list;
        }
        /// <summary>
        /// 未发货的数量
        /// </summary>
        /// <returns></returns>
        public int shu()
        {
            string str = "Pro_State";
            return Convert.ToInt32(p.ExecScalar(str));
        }
        /// <summary>
        /// 未发货的数量
        /// </summary>
        /// <returns></returns>
        public int shu1()
        {
            string str = "Pro_State1";
            return Convert.ToInt32(p.ExecScalar(str));
        }
        /// <summary>
        /// 当天的订单
        /// </summary>
        /// <returns></returns>
        public int ShowOneday()
        {
            string sql = "select COUNT(*) from Shipments where DateDiff(dd,Shipments_data,getdate())=0";
            return Convert.ToInt32(p.ExecScalar(sql));
        }
        /// <summary>
        /// 三天内
        /// </summary>
        /// <returns></returns>
        public int ShowThreeday()
        {
            string sql = "select COUNT(*) from Shipments where DateDiff(dd,Shipments_data,getdate())<3";
            return Convert.ToInt32(p.ExecScalar(sql));
        }
        /// <summary>
        /// 六天内
        /// </summary>
        /// <returns></returns>
        public int ShowSixday()
        {
            string sql = "select COUNT(*) from Shipments where DateDiff(dd,Shipments_data,getdate())<6";
            return Convert.ToInt32(p.ExecScalar(sql));
        }
        /// <summary>
        /// 七天内
        /// </summary>
        /// <returns></returns>
        public int ShowSevenday()
        {
            string sql = "select COUNT(*) from Shipments where DateDiff(dd,Shipments_data,getdate())<7";
            return Convert.ToInt32(p.ExecScalar(sql));
        }
    }
}
